package com.dimeina.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.dimeina.dao.ProDAO;
import com.dimeina.util.BaseDAO;
import com.dimeina.vo.PageVo;
import com.dimeina.vo.ProPicPath;
import com.dimeina.vo.ProView;
import com.dimeina.vo.Product;

public class ProDAOImpl extends BaseDAO implements ProDAO {

	// 通过SQL语句查找
	// 私有方法 对“结果集”进行二次封装 返回对象集合
	@SuppressWarnings("unused")
	private List<Product> findBySql(String sql, Object... values) {
		List<Product> proList = new ArrayList<Product>();
		rs = this.executeQuery(sql, values);
		try {
			while (rs.next()) {
				Product pro = new Product();
				pro.setId(rs.getInt("id"));
				pro.setName(rs.getString("name"));
				pro.setTopCategoryID(rs.getInt("topCategoryID"));
				pro.setPCategoryID(rs.getInt("PCategoryID"));
				pro.setCategoryID(rs.getInt("categoryID"));
				pro.setPrice(rs.getDouble("price"));
				pro.setCapacity(rs.getInt("capacity"));
				pro.setDescription(rs.getString("description"));
				pro.setIngredient(rs.getString("ingredient"));
				pro.setValue(rs.getInt("value"));
				pro.setInventory(rs.getInt("inventory"));
				pro.setContent(rs.getString("content"));
				pro.setCreateDate(rs.getTimestamp("createDate"));
				pro.setModifyDate(rs.getTimestamp("modifyDate"));
				// 添加到集合中
				proList.add(pro);
			}
		} catch (SQLException e) {
			System.out.println("通过SQL语句查找_异常！");
			e.printStackTrace();
		} finally {
			closeAll(rs, pstmt, conn);
		}
		return proList;
	}

	// 添加商品详细信息
	// 返回插入记录的“主键”
	public int addPro(Product pro) {
		int record = 0;
		List<Object> values = new ArrayList<Object>();
		String sqlPro = "INSERT INTO `product` "
				+ "(`name`,`topCategoryID`,`PCategoryID`,`categoryID`,`price`,`capacity`,`description`,"
				+ "`ingredient`,`value`,`inventory`,`content`,`modifyDate`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
		values.add(pro.getName());
		values.add(pro.getTopCategoryID());
		values.add(pro.getPCategoryID());
		values.add(pro.getCategoryID());
		values.add(pro.getPrice());
		values.add(pro.getCapacity());
		values.add(pro.getDescription());
		values.add(pro.getIngredient());
		values.add(pro.getValue());
		values.add(pro.getInventory());
		values.add(pro.getContent());
		values.add(pro.getModifyDate());
		// 执行语句
		executeUpdate(sqlPro, values.toArray());
		record = findProRecordByName(pro.getName());
		return record;
	}

	// 添加商品图片
	// 用产品对象的id(主键)和获取的图片 创ProPicPath对象 再插入
	public int addProPic(ProPicPath ppp) {
		// ★★★★★在Service层一定要把插入的商品详细信息的id setID到ProPicPath
		List<Object> values = new ArrayList<Object>();
		// 插入产品后 获取数据库中刚插入记录的id主键，作为图片表的id主键插入
		String sqlPic = "INSERT INTO `propicpath` (`id`,`picPath1`,`picPath2`,`picPath3`,`picPath4`,`picPath5`,`createDate`,`modifyDate`) "
				+ "VALUES (?,?,?,?,?,?,?,?)";
		values.add(ppp.getId());
		values.add(ppp.getPicPath1());
		values.add(ppp.getPicPath2());
		values.add(ppp.getPicPath3());
		values.add(ppp.getPicPath4());
		values.add(ppp.getPicPath5());
		values.add(ppp.getCreateDate());
		values.add(ppp.getModifyDate());
		int resultPic = executeUpdate(sqlPic, values.toArray());
		return resultPic;
	}

	// 删除商品
	public int delPro(int id) {
		int result = 0;
		String sql = "DELETE FROM `product` WHERE id = ?";
		try {
			result = executeUpdate(sql, id);
		} catch (Exception e) {
			System.out.println("删除新闻_DAO异常！");
		} finally {
			closeAll(rs, pstmt, conn);
		}
		return result;
	}

	// 修改商品详情
	public int updPro(Product pro) {
		int result = 0;
		List<Object> values = new ArrayList<Object>();
		String sql = "UPDATE `product` SET `name`=?,`topCategoryID`=?,`PCategoryID`=?,"
				+ "`categoryID`=?,`price`=?,`capacity`=?,`description`=?,`ingredient`=?,"
				+ "`value`=?,`inventory`=?,`content`=?,`modifyDate`=? WHERE `id` = ?";
		values.add(pro.getName());
		values.add(pro.getTopCategoryID());
		values.add(pro.getPCategoryID());
		values.add(pro.getCategoryID());
		values.add(pro.getPrice());
		values.add(pro.getCapacity());
		values.add(pro.getDescription());
		values.add(pro.getIngredient());
		values.add(pro.getValue());
		values.add(pro.getInventory());
		values.add(pro.getContent());
		values.add(pro.getModifyDate());
		values.add(pro.getId());
		try {
			result = executeUpdate(sql, values.toArray());
		} catch (Exception e) {
			System.out.println("修改商品_DAO层异常！");
		} finally {
			closeAll(rs, pstmt, conn);
		}
		return result;
	}

	
	// 修改商品图片
	// 返回int 修改成功的记录条数
	public int updProPic(ProPicPath proPic) {
		int result = 0;
		List<Object> values = new ArrayList<Object>();
		String sql = "UPDATE `propicpath` SET `picPath1`=?,`picPath2`=?,`picPath3`=?,`picPath4`=?,"
				   + "`picPath5`=?,`modifyDate`=? WHERE `id` = ?";
		values.add(proPic.getPicPath1());
		values.add(proPic.getPicPath2());
		values.add(proPic.getPicPath3());
		values.add(proPic.getPicPath4());
		values.add(proPic.getPicPath5());
		values.add(proPic.getModifyDate());
		values.add(proPic.getId());
		try {
			result = executeUpdate(sql, values.toArray());
		} catch (Exception e) {
			System.out.println("修改商品图片_DAO层异常！");
		} finally {
			closeAll(rs, pstmt, conn);
		}
		return result;
	}
	
	
	// 根据id搜索商品详情
	// 返回Product
	public Product findProByID(int id) {
		Product pro = null;
		String sql = "SELECT * FROM `product` WHERE `id` = ?";
		rs = this.executeQuery(sql, id);
		try {
			while (rs.next()) {
				pro = new Product();
				pro.setId(rs.getInt("id"));
				pro.setName(rs.getString("name"));
				pro.setTopCategoryID(rs.getInt("topCategoryID"));
				pro.setPCategoryID(rs.getInt("PCategoryID"));
				pro.setCategoryID(rs.getInt("categoryID"));
				pro.setPrice(rs.getDouble("price"));
				pro.setCapacity(rs.getInt("capacity"));
				pro.setDescription(rs.getString("description"));
				pro.setIngredient(rs.getString("ingredient"));
				pro.setValue(rs.getInt("value"));
				pro.setInventory(rs.getInt("inventory"));
				pro.setContent(rs.getString("content"));
				pro.setCreateDate(rs.getTimestamp("createDate"));
				pro.setModifyDate(rs.getTimestamp("modifyDate"));
			}
		} catch (SQLException e) {
			System.out.println("根据id搜索商品详情_DAO层异常！");
			e.printStackTrace();
		} finally {
			closeAll(rs, pstmt, conn);
		}
		return pro;
	}

	// 根据id搜索商品详情ProView
	// 返回ProView
	public ProView findProViewByID(int id) {
		ProView proView = null;
		Product pro = null;
		pro = findProByID(id);
		// 声明
		String topCategoryID = null;
		String PCategoryID = null;
		String categoryID = null;
		int categoryIDInt = pro.getCategoryID();
		String sql = "SELECT c.id,c.name AS `self`,p.name AS `parent` "
				+ "FROM `category` AS c LEFT JOIN `category` AS  p ON c.pid = p.id WHERE c.id = ?";
		// 只有一条记录
		rs = executeQuery(sql, new Object[] { categoryIDInt });
		try {
			while (rs.next()) {
				try {
					categoryID = rs.getString("self");
					PCategoryID = rs.getString("parent");
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			System.out.println("根据id搜索商品详情ProView_DAO层异常！");
			e.printStackTrace();
		}
		// 获取顶级分类ID
		String sql2 = "SELECT `name` FROM `category` WHERE `id` = ( SELECT `pid` FROM `category` WHERE `id` = ( SELECT `pid` FROM `category` WHERE `id` = ? ) )";
		ResultSet rs2 = null;
		rs2 = executeQuery(sql2, new Object[] { categoryIDInt });
		try {
			while (rs2.next()) {
				topCategoryID = rs2.getString("name");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 创建新的ProView
		proView = new ProView(pro.getId(), topCategoryID, PCategoryID, categoryID, pro.getName(),
				pro.getDescription(), pro.getIngredient(), pro.getValue(), pro.getInventory(), pro.getContent(),
				pro.getPrice(), pro.getCapacity(),pro.getCreateDate(), pro.getModifyDate());
		return proView;
	}

	// 根据id搜索商品图片
	// 返回ProPicPath
	public ProPicPath findProPicByID(int id) {
		ProPicPath ppp = null;
		String sql = "SELECT * FROM `propicpath` WHERE `id` = ?";
		rs = this.executeQuery(sql, id);
		try {
			while (rs.next()) {
				ppp = new ProPicPath();
				ppp.setId(rs.getInt("id"));
				ppp.setPicPath1(rs.getString("picPath1"));
				ppp.setPicPath2(rs.getString("picPath2"));
				ppp.setPicPath3(rs.getString("picPath3"));
				ppp.setPicPath4(rs.getString("picPath4"));
				ppp.setPicPath5(rs.getString("picPath5"));
				ppp.setCreateDate(rs.getTimestamp("createDate"));
				ppp.setModifyDate(rs.getTimestamp("modifyDate"));
			}
		} catch (SQLException e) {
			System.out.println("根据id搜索商品图片_DAO层异常！");
			e.printStackTrace();
		} finally {
			closeAll(rs, pstmt, conn);
		}
		return ppp;
	}

	// 根据Name搜索商品
	public Product findProByName(String name) {
		Product pro = null;
		String sql = "SELECT * FROM `product` WHERE `name` = ?";
		rs = this.executeQuery(sql, name);
		try {
			while (rs.next()) {
				pro = new Product();
				pro.setId(rs.getInt("id"));
				pro.setName(rs.getString("name"));
				pro.setTopCategoryID(rs.getInt("topCategoryID"));
				pro.setPCategoryID(rs.getInt("PCategoryID"));
				pro.setCategoryID(rs.getInt("categoryID"));
				pro.setPrice(rs.getDouble("price"));
				pro.setCapacity(rs.getInt("capacity"));
				pro.setDescription(rs.getString("description"));
				pro.setIngredient(rs.getString("ingredient"));
				pro.setValue(rs.getInt("value"));
				pro.setInventory(rs.getInt("inventory"));
				pro.setContent(rs.getString("content"));
				pro.setCreateDate(rs.getTimestamp("createDate"));
				pro.setModifyDate(rs.getTimestamp("modifyDate"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(rs, pstmt, conn);
		}
		return pro;
	}

	// 根据Name搜索商品记录“主键”
	public int findProRecordByName(String name) {
		int result = 0;
		String sql = "SELECT * FROM `product` WHERE `name` = ?";
		ResultSet rs = executeQuery(sql, name);
		try {
			while (rs.next()) {
				result = rs.getInt("id");
			}
		} catch (SQLException e) {
			System.out.println("根据Name搜索商品记录“主键”_异常！");
			e.printStackTrace();
		} finally {
			closeAll(rs, pstmt, conn);
		}
		return result;
	}

	// 根据Name模糊搜索商品
	public List<Product> findProByDimName(String dimName) {
		ArrayList<Product> proList = new ArrayList<Product>();
		String sql = "SELECT * FROM `product` WHERE `name` LIKE ? ";
		// 调用方法，传查询语句，因为没有占位符，所有传String实例，
		// 连接%用来模糊匹配！
		this.rs = this.executeQuery(sql, "%" + dimName + "%");
		try {
			while (this.rs.next()) {
				Product pro = new Product();
				pro.setId(rs.getInt("id"));
				pro.setName(rs.getString("name"));
				pro.setTopCategoryID(rs.getInt("topCategoryID"));
				pro.setPCategoryID(rs.getInt("PCategoryID"));
				pro.setCategoryID(rs.getInt("categoryID"));
				pro.setPrice(rs.getDouble("price"));
				pro.setCapacity(rs.getInt("capacity"));
				pro.setDescription(rs.getString("description"));
				pro.setIngredient(rs.getString("ingredient"));
				pro.setValue(rs.getInt("value"));
				pro.setInventory(rs.getInt("inventory"));
				pro.setContent(rs.getString("content"));
				pro.setCreateDate(rs.getTimestamp("createDate"));
				pro.setModifyDate(rs.getTimestamp("modifyDate"));
				// 将对象添加到集合中
				proList.add(pro);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs, pstmt, conn);
		}
		return proList;
	}

	// 通过PCategoryID搜索所有同级别的商品
	public List<Product> findProByPCategoryID(int PCategoryID) {
		ArrayList<Product> proList = new ArrayList<Product>();
		String sql = "SELECT * FROM `product` WHERE `PCategoryID` = ?";
		this.rs = this.executeQuery(sql, PCategoryID);
		try {
			while (this.rs.next()) {
				Product pro = new Product();
				pro.setId(rs.getInt("id"));
				pro.setName(rs.getString("name"));
				pro.setTopCategoryID(rs.getInt("topCategoryID"));
				pro.setPCategoryID(rs.getInt("PCategoryID"));
				pro.setCategoryID(rs.getInt("categoryID"));
				pro.setPrice(rs.getDouble("price"));
				pro.setCapacity(rs.getInt("capacity"));
				pro.setDescription(rs.getString("description"));
				pro.setIngredient(rs.getString("ingredient"));
				pro.setValue(rs.getInt("value"));
				pro.setInventory(rs.getInt("inventory"));
				pro.setContent(rs.getString("content"));
				pro.setCreateDate(rs.getTimestamp("createDate"));
				pro.setModifyDate(rs.getTimestamp("modifyDate"));
				// 将对象添加到集合中
				proList.add(pro);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs, pstmt, conn);
		}
		return proList;
	}

	// 搜索所有商品Pro
	public List<Product> findAllPro() {
		ArrayList<Product> proList = new ArrayList<Product>();
		String sql = "SELECT * FROM `product` ORDER BY `createDate` DESC";
		this.rs = this.executeQuery(sql, new Object[] {});
		try {
			while (this.rs.next()) {
				Product pro = new Product();
				pro.setId(rs.getInt("id"));
				pro.setName(rs.getString("name"));
				pro.setTopCategoryID(rs.getInt("topCategoryID"));
				pro.setPCategoryID(rs.getInt("PCategoryID"));
				pro.setCategoryID(rs.getInt("categoryID"));
				pro.setPrice(rs.getDouble("price"));
				pro.setCapacity(rs.getInt("capacity"));
				pro.setDescription(rs.getString("description"));
				pro.setIngredient(rs.getString("ingredient"));
				pro.setValue(rs.getInt("value"));
				pro.setInventory(rs.getInt("inventory"));
				pro.setContent(rs.getString("content"));
				pro.setCreateDate(rs.getTimestamp("createDate"));
				pro.setModifyDate(rs.getTimestamp("modifyDate"));
				// 将对象添加到集合中
				proList.add(pro);
			}
		} catch (Exception e) {
			System.out.println("搜索所有商品_DAO异常");
			e.printStackTrace();
		} finally {
			closeAll(rs, pstmt, conn);
		}
		return proList;
	}

	// 搜索所有商品ProView
	public List<ProView> findAllProView() {
		List<Product> proList = new ArrayList<Product>();
		List<ProView> proViewList = new ArrayList<ProView>();
		proList = findAllPro();
		// 声明
		String topCategoryID = null;
		String PCategoryID = null;
		String categoryID = null;
		for (Product pro : proList) {
			int categoryIDInt = pro.getCategoryID();
			String sql = "SELECT c.id,c.name AS `self`,p.name AS `parent` "
					+ "FROM `category` AS c LEFT JOIN `category` AS  p ON c.pid = p.id WHERE c.id = ?";
			// 只有一条记录
			rs = executeQuery(sql, new Object[] { categoryIDInt });
			try {
				while (rs.next()) {
					try {
						categoryID = rs.getString("self");
						PCategoryID = rs.getString("parent");
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			} catch (SQLException e) {
				System.out.println("搜索所有商品ProView_DAO层异常！");
				e.printStackTrace();
			}
			// 获取顶级分类ID
			String sql2 = "SELECT `name` FROM `category` WHERE `id` = ( SELECT `pid` FROM `category` WHERE `id` = ( SELECT `pid` FROM `category` WHERE `id` = ? ) )";
			ResultSet rs2 = null;
			rs2 = executeQuery(sql2, new Object[] { categoryIDInt });
			try {
				while (rs2.next()) {
					topCategoryID = rs2.getString("name");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

			// 创建新的ProView
			ProView proView = new ProView(pro.getId(), topCategoryID, PCategoryID, categoryID, pro.getName(),
					pro.getDescription(), pro.getIngredient(), pro.getValue(), pro.getInventory(), pro.getContent(),
					pro.getPrice(), pro.getCapacity(),pro.getCreateDate(), pro.getModifyDate());
			// 添加进集合
			proViewList.add(proView);
		}
		return proViewList;
	}

	// 获得总记录数
	public int getTotalCount() {
		String sql = "SELECT COUNT(1) AS `pro` FROM `product`";
		int total = 0;
		rs = this.executeQuery(sql, new Object[] {});
		try {
			if (rs.next()) {
				total = rs.getInt("pro");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(rs, pstmt, conn);
		}
		return total;
	}

	// 分页获取商品
	public PageVo<ProView> findProByPaging(int curPageNo, int pageSize) {
		return null;
		/*
		 * PageVo<ProView> pageVo = new PageVo<ProView>(); List<ProView> proList
		 * = new ArrayList<ProView>(); pageVo.setPageno(curPageNo);
		 * pageVo.setPagesize(pageSize);
		 * 
		 * String sql =
		 * "SELECT `id`,`name`,`categoryID`,`price`,`createDate`,`modifyDate` "
		 * + "FROM `product` " + "ORDER BY `createdate` DESC LIMIT ?,?"; int
		 * startRow = pageVo.getOffset(); // 此信息含有：id name categoryID price
		 * createDate modifyDate rs = executeQuery(sql, new Object[] { startRow,
		 * pageSize }); try { while (rs.next()) { // 声明 String topCategoryID =
		 * null; String PCategoryID = null; String categoryID = null; // 获取属性
		 * int id = rs.getInt("id"); String name = rs.getString("name"); int
		 * categoryIDInt = rs.getInt("categoryID"); // 这个用来做变量，不显示 double price
		 * = rs.getDouble("price"); Timestamp createDate =
		 * rs.getTimestamp("createdate"); Timestamp modifyDate =
		 * rs.getTimestamp("modifydate"); // categoryIDInt 作为参数 获取“自身分类ID” 和
		 * “父级ID” String sql2 =
		 * "SELECT c.id,c.name AS `self`,p.name AS `parent` " +
		 * "FROM `category` AS c LEFT JOIN `category` AS  p ON c.pid = p.id WHERE c.id = ?"
		 * ; ResultSet rs2 = null; // 只有一条记录 rs2 = executeQuery(sql2, new
		 * Object[] { categoryIDInt }); while (rs2.next()) { categoryID =
		 * rs2.getString("self"); PCategoryID = rs2.getString("parent"); } //
		 * 获取顶级分类ID String sql3 =
		 * "SELECT `name` FROM `category` WHERE `id` = ( SELECT `pid` FROM `category` WHERE `id` = ( SELECT `pid` FROM `category` WHERE `id` = ? ) )"
		 * ; ResultSet rs3 = null; rs3 = executeQuery(sql3, new Object[] {
		 * categoryIDInt }); while (rs3.next()) { topCategoryID =
		 * rs3.getString("name"); }
		 * 
		 * ProView proView = new ProView(id, topCategoryID, PCategoryID,
		 * categoryID, name, price, createDate, modifyDate);
		 * 
		 * proList.add(proView); } } catch (SQLException e) {
		 * e.printStackTrace(); } finally { closeAll(rs, pstmt, conn); }
		 * 
		 * pageVo.setList(proList); int totalCount = getTotalCount();
		 * pageVo.setTotalcount(totalCount); return pageVo;
		 */
	}

	

}
